from flask import Blueprint, render_template, jsonify
from flask_login import login_required
from app import db
from app.models import PlantingData, CropVariety
from sqlalchemy import func
from datetime import datetime, timedelta

bp = Blueprint('analysis', __name__, url_prefix='/analysis')

@bp.route('/structure')
@login_required
def structure():
    return render_template('analysis/structure.html')

@bp.route('/get-structure-data')
@login_required
def get_structure_data():
    try:
        # 1. 获取种植面积分布数据
        area_distribution = db.session.query(
            PlantingData.crop_type,
            func.sum(PlantingData.area).label('total_area')
        ).group_by(PlantingData.crop_type).all()

        # 2. 获取区域分布数据
        region_distribution = db.session.query(
            PlantingData.region,
            func.sum(PlantingData.area).label('total_area')
        ).group_by(PlantingData.region).all()

        # 3. 获取时间趋势数据
        # 获取最近12个月的数据
        end_date = datetime.now()
        start_date = end_date - timedelta(days=365)
        
        trend_data = db.session.query(
            PlantingData.planting_date,
            PlantingData.crop_type,
            func.sum(PlantingData.area).label('total_area')
        ).filter(
            PlantingData.planting_date.between(start_date, end_date)
        ).group_by(
            PlantingData.planting_date,
            PlantingData.crop_type
        ).order_by(PlantingData.planting_date).all()

        # 处理时间趋势数据
        dates = sorted(list(set(item.planting_date.strftime('%Y-%m-%d') for item in trend_data)))
        crops = sorted(list(set(item.crop_type for item in trend_data)))
        values = {crop: [0] * len(dates) for crop in crops}
        
        for item in trend_data:
            date_index = dates.index(item.planting_date.strftime('%Y-%m-%d'))
            values[item.crop_type][date_index] = float(item.total_area)

        return jsonify({
            'area_distribution': [
                {'name': item.crop_type, 'value': float(item.total_area)}
                for item in area_distribution
            ],
            'region_distribution': [
                {'name': item.region, 'value': float(item.total_area)}
                for item in region_distribution
            ],
            'trend_data': {
                'dates': dates,
                'crops': crops,
                'values': values
            }
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@bp.route('/benefit')
@login_required
def benefit():
    return render_template('analysis/benefit.html')

@bp.route('/get-benefit-data')
@login_required
def get_benefit_data():
    try:
        # 1. 获取作物产值分布数据
        output_distribution = db.session.query(
            PlantingData.crop_type,
            func.sum(PlantingData.area * db.cast(
                db.func.regexp_replace(CropVariety.yield_potential, '[^0-9.]', ''),
                db.Float
            ) * db.cast(
                db.func.regexp_replace(CropVariety.market_value, '[^0-9.]', ''),
                db.Float
            )).label('total_value')
        ).join(
            CropVariety,
            PlantingData.crop_type == CropVariety.crop_type
        ).group_by(PlantingData.crop_type).all()

        # 2. 获取区域效益数据
        region_benefit = db.session.query(
            PlantingData.region,
            func.sum(PlantingData.area).label('total_area'),
            func.sum(PlantingData.area * db.cast(
                db.func.regexp_replace(CropVariety.yield_potential, '[^0-9.]', ''),
                db.Float
            ) * db.cast(
                db.func.regexp_replace(CropVariety.market_value, '[^0-9.]', ''),
                db.Float
            )).label('total_value')
        ).join(
            CropVariety,
            PlantingData.crop_type == CropVariety.crop_type
        ).group_by(PlantingData.region).all()

        # 3. 获取时间趋势数据
        end_date = datetime.now()
        start_date = end_date - timedelta(days=365)
        
        trend_data = db.session.query(
            PlantingData.planting_date,
            PlantingData.crop_type,
            func.sum(PlantingData.area * db.cast(
                db.func.regexp_replace(CropVariety.yield_potential, '[^0-9.]', ''),
                db.Float
            ) * db.cast(
                db.func.regexp_replace(CropVariety.market_value, '[^0-9.]', ''),
                db.Float
            )).label('total_value')
        ).join(
            CropVariety,
            PlantingData.crop_type == CropVariety.crop_type
        ).filter(
            PlantingData.planting_date.between(start_date, end_date)
        ).group_by(
            PlantingData.planting_date,
            PlantingData.crop_type
        ).order_by(PlantingData.planting_date).all()

        # 处理时间趋势数据
        dates = sorted(list(set(item.planting_date.strftime('%Y-%m-%d') for item in trend_data)))
        crops = sorted(list(set(item.crop_type for item in trend_data)))
        values = {crop: [0] * len(dates) for crop in crops}
        
        for item in trend_data:
            date_index = dates.index(item.planting_date.strftime('%Y-%m-%d'))
            values[item.crop_type][date_index] = float(item.total_value) / 10000  # 转换为万元

        return jsonify({
            'output_distribution': [
                {
                    'name': item.crop_type,
                    'value': float(item.total_value) / 10000  # 转换为万元
                }
                for item in output_distribution
            ],
            'region_benefit': [
                {
                    'name': item.region,
                    'total_value': float(item.total_value) / 10000,  # 转换为万元
                    'per_mu_value': float(item.total_value) / float(item.total_area)  # 计算亩产值
                }
                for item in region_benefit
            ],
            'trend_data': {
                'dates': dates,
                'crops': crops,
                'values': values
            }
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500 